package com.bbva.gptf.back.dominio;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.NotNull;

import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.entity.RooEntity;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.tostring.RooToString;

import com.bbva.gptf.back.transformador.Tgpt005BitacoraCargaTransformador;
import com.bbva.gptf.dto.bean.BitacoraCarga;

@RooJavaBean
@RooToString
@RooEntity(versionField = "", table = "TGPT005_BITA_CARGA", schema = "DBSGPT")
@RooDbManaged(automaticallyDelete = true)
public class Tgpt005BitaCarga {
	public static BigDecimal insertarBitacora(final BitacoraCarga bitacoraCarga) {
		final Tgpt005BitacoraCargaTransformador bitacoraTranformador = new Tgpt005BitacoraCargaTransformador();
		final Tgpt005BitaCarga bitacoraCargaBD = bitacoraTranformador
				.convert(bitacoraCarga);
		bitacoraCargaBD.persist();
		bitacoraCarga.setCdCarga(bitacoraCargaBD.getCdCarga());
		return bitacoraCarga.getCdCarga();
	}

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO, generator = "sq_bitacora_carga")
	@SequenceGenerator(name = "sq_bitacora_carga", sequenceName = "SQ_BITACORA_CARGA")
	@Column(name = "CD_CARGA")
	private BigDecimal cdCarga;

	@Column(name = "FH_CARGA")
	@NotNull
	@Temporal(TemporalType.TIMESTAMP)
	@DateTimeFormat(style = "S-")
	private Date fhCarga;

	@Column(name = "FH_UMO")
	@Temporal(TemporalType.DATE)
	@DateTimeFormat(style = "S-")
	private Date fhUmo;

	@Column(name = "TM_UMO")
	@Temporal(TemporalType.TIMESTAMP)
	@DateTimeFormat(style = "S-")
	private Date tmUmo;
	
	public static List<Tgpt005BitaCarga> findTgpt005FicheroCargaIntradia(final BigDecimal cdFichero) {
		try {
			return entityManager()
			.createQuery(
					"SELECT bc FROM Tgpt005BitaCarga bc WHERE bc.cdCarga in( "
					+ " SELECT MAX(bc2.cdCarga) FROM Tgpt005BitaCarga bc2 WHERE "
					+ " UPPER(SUBSTRING(bc2.nbFichero, 1, CASE WHEN INSTR(bc2.nbFichero, '.') <> 0 THEN (INSTR(bc2.nbFichero, '.') -1) ELSE LENGTH(bc2.nbFichero) END)) in ( "
					+ " SELECT UPPER(SUBSTRING(bc1.nbFichero, 1, CASE WHEN INSTR(bc1.nbFichero, '.') <> 0 THEN (INSTR(bc1.nbFichero, '.') -1) ELSE LENGTH(bc1.nbFichero) END)) "
					+ " FROM Tgpt005BitaCarga bc1 "
					+ " WHERE bc1.cdFichero.cdFichero = :cdFichero "
					+ " AND TRUNC(bc1.fhCarga) = TRUNC(sysdate) "
					+ " GROUP BY UPPER(SUBSTRING(bc1.nbFichero, 1, CASE WHEN INSTR(bc1.nbFichero, '.') <> 0 THEN (INSTR(bc1.nbFichero, '.') -1) ELSE LENGTH(bc1.nbFichero) END))) "
					+ " GROUP BY UPPER(SUBSTRING(bc2.nbFichero, 1, CASE WHEN INSTR(bc2.nbFichero, '.') <> 0 THEN (INSTR(bc2.nbFichero, '.') -1) ELSE LENGTH(bc2.nbFichero) END))) "
					+ " ORDER BY bc.cdCarga DESC",
					Tgpt005BitaCarga.class).setParameter("cdFichero", cdFichero).setMaxResults(10).getResultList();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		return new ArrayList<Tgpt005BitaCarga>();
		
	}

}
